﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBUtility;


namespace CUREDataAccessLayers
{
    public class AuthorityDal
    {
        /// <summary>
        /// 获取用户组的详细信息
        /// </summary>
        /// <returns>用户组的详细信息</returns>
        public DataTable GetGroupData()
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append("select t.group_id,t.group_name,t.group_info from groupmanager t");
            try
            {
                dt = DbHelperOra.QueryTable(sql.ToString());
               // this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 新增用户组信息
        /// </summary>
        /// <param name="where">字典参数</param>
        /// <returns>true 新增成功/false 新增失败</returns>
        public bool addGroupData(IDictionary<string, object> where)
        {
            bool jude = false;
            StringBuilder sql = new StringBuilder();
            int id = GetMaxId();//组ID
            string groupInfo = "";//组信息
            string groupName = "";//组名称
            string createDate = DateTime.Now.ToString("yyyyMMdd");//创建日期
            if (where["name"] != null && where["name"].ToString() != "")
            {
                groupInfo = "Group" + id;
                groupName = where["name"].ToString().Trim();
                sql.Append("insert into  groupmanager values(" + id + ",'" + groupName + "','" + groupInfo + "','" + createDate + "','','')");
            }
            if (!findGroupInfoByName(where["name"].ToString()))
            {
                try
                {
                    int i = DbHelperOra.ExecuteSql(sql.ToString());
                    //int i = this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
                    if (i == 1)
                    {
                        //添加成功
                        jude = true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                jude = false;
            }

            return jude;

        }

        /// <summary>
        /// 更新用户组信息
        /// </summary>
        /// <param name="where">字典参数</param>
        /// <returns>true 更新成功/false 更新失败</returns>
        public bool UpdateGroupData(IDictionary<string, object> where)
        {
            bool judge = false;
            StringBuilder sql = new StringBuilder();

            if (where.ContainsKey("info") && where["info"] != null && where["info"].ToString() != "" && where.ContainsKey("name") && where["name"] != null && where["name"].ToString() != "")
            {
                string info = where["info"].ToString();
                string name = where["name"].ToString();
                sql.Append("update groupmanager  set group_name='" + name.Trim() + "' where group_info='" + info + "'");
                try
                {
                    int i = DbHelperOra.ExecuteSql(sql.ToString());
                    //int i = this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
                    if (i > 0)
                    {
                        //成功
                        judge = true;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return judge;
        }

        /// <summary>
        /// 删除用户组信息
        /// </summary>
        /// <param name="where">字典参数</param>
        /// <returns>true 删除成功/false 删除失败</returns>
        public bool DeleteGroupData(IDictionary<string, object> where)
        {
            bool jude = false;
            StringBuilder sql = new StringBuilder();

            string groupinfo = string.Empty;
            if (where["name"] != null && where["name"].ToString() != "")
            {
                groupinfo = where["name"].ToString();
            }
            sql.Append("begin ");
            sql.Append("delete from groupmanager  where group_info='" + groupinfo + "';");
            sql.Append(" delete from actiongroup t where t.group_id='" + groupinfo.Substring(5, groupinfo.Length-5) + "';");
            sql.Append(" commit;");
            sql.Append(" end;");
            try
            {
                int i = DbHelperOra.ExecuteSql(sql.ToString());
                //int i = this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
                jude = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return jude;
        }

        /// <summary>
        /// 判断组名是否已经存在
        /// </summary>
        /// <param name="name">组名称</param>
        /// <returns>true，已经存在，false，不存在</returns>
        public bool findGroupInfoByName(string name)
        {
            bool jude = false;
            string groupname = string.Empty;
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("select * from groupmanager t where t.group_info='" + name + "'");
                Object obj = DbHelperOra.GetSingle(sql.ToString());
                if (!Object.Equals(obj, null))
                {
                    groupname = obj.ToString();
                }
               // groupname = this.DbHelperInstance.ExecuteScalar<string>(sql);
                if (groupname != null && groupname != "")
                {
                    jude = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return jude;
        }

        /// <summary>
        /// 获取最大的id
        /// </summary>
        /// <returns>返回最大的id+1</returns>
        public int GetMaxId()
        {
            int maxid = 0;
            StringBuilder commandText = new StringBuilder();
            try
            {
                commandText.Append("select max(to_number(group_id))+1 from groupmanager t");
                Object obj = DbHelperOra.GetSingle(commandText.ToString());
                if (!Object.Equals(obj, null))
                {
                    maxid = Convert.ToInt32(obj);
                }
               // maxid = (int)this.DbHelperInstance.ExecuteScalar<decimal>(commandText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return maxid;
        }


        /// <summary>
        /// 根据组的info获取到组的name
        /// </summary>
        /// <param name="info">组info</param>
        /// <returns>组name</returns>
        public string GetGroupNameByInfo(string info)
        {
            string groupname = string.Empty;
            StringBuilder sql = new StringBuilder();
            sql.Append("select t.group_name from groupmanager t where t.group_info='" + info + "'");
            try
            {
                Object obj = DbHelperOra.GetSingle(sql.ToString());
                if (!Object.Equals(obj, null))
                {
                    groupname = obj.ToString();
                }
               // groupname = this.DbHelperInstance.ExecuteScalar<string>(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return groupname;
        }

        /// <summary>
        /// 根据组的info获取到组的id
        /// </summary>
        /// <param name="info">组id</param>
        /// <returns>组name</returns>
        public string GetGroupNameByID(string id)
        {
            string groupname = string.Empty;
            StringBuilder sql = new StringBuilder();
            sql.Append("select t.group_name from groupmanager t where t.group_id='" + id + "'");
            try
            {
                Object obj = DbHelperOra.GetSingle(sql.ToString());
                if (!Object.Equals(obj, null))
                {
                    groupname = obj.ToString();
                }
                //groupname = this.DbHelperInstance.ExecuteScalar<string>(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return groupname;
        }


        /// <summary>
        /// 获取模块列表
        /// </summary>
        /// <returns>模块信息</returns>
        public DataTable getActionList()
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append("select a.action_columnname,a.action_columnid,t.action,t.action_name from action t,actioncolumn a where t.action_columnid=a.action_columnid order by a.action_columnid ");
            try
            {
                dt = DbHelperOra.QueryTable(sql.ToString());
                //this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 修改用户组的权限
        /// </summary>
        /// <param name="where">字典参数</param>
        /// <returns>true 修改成功/false 修改失败</returns>
        public bool EditGroupAuthority(IDictionary<string, object> where)
        {
            bool judge = false;

            string groupinfo = "";
            if (where["info"] != null && where["info"].ToString() != "")
            {
                groupinfo = where["info"].ToString();
            }
            if (where["strRights"] != null && where["strRights"].ToString() != "")
            {
                RemoveRights(groupinfo.Trim());
                string[] str = where["strRights"].ToString().Split(',');
                string date = DateTime.Now.ToString("yyyyMMdd");
                if (str.Length > 0)
                {
                    if (findGroupByName(groupinfo))
                    {
                        string groupId = getGroupID(groupinfo);
                        RemoveRights(groupId);
                        for (int i = 0; i < str.Length; i++)
                        {
                            StringBuilder sql = new StringBuilder();
                            sql.Append("insert into actiongroup values('" + str[i].ToString().Trim() + "'," + groupId + ",'','','" + date + "')");
                            try
                            {
                                DbHelperOra.ExecuteSql(sql.ToString());
                                //this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
                                judge = true;
                            }
                            catch (Exception ex)
                            {
                                throw ex;
                            }
                        }
                    }
                }
            }
            else
            {
                if (groupinfo != null && groupinfo != "")
                {
                    RemoveRights(groupinfo.Trim());
                    judge = true;
                }
            }
            return judge;
        }

        /// <summary>
        /// 修改用户组的PDA权限
        /// </summary>
        /// <param name="where">字典参数</param>
        /// <returns>true 修改成功/false 修改失败</returns>
        public bool EditGroupAuthorityPDA(IDictionary<string, object> where)
        {
            bool judge = false;

            string groupinfo = "";
            if (where["info"] != null && where["info"].ToString() != "")
            {
                groupinfo = where["info"].ToString();
            }
            if (where["strRights"] != null && where["strRights"].ToString() != "")
            {
                RemoveRightsPDA(groupinfo.Trim());
                string[] str = where["strRights"].ToString().Split(',');
                string date = DateTime.Now.ToString("yyyyMMdd");
                if (str.Length > 0)
                {
                    if (findGroupByName(groupinfo))
                    {
                        string groupId = getGroupID(groupinfo);
                        RemoveRights(groupId);
                        for (int i = 0; i < str.Length; i++)
                        {
                            StringBuilder sql = new StringBuilder();
                            sql.Append("insert into actiongrouppda values('" + str[i].ToString().Trim() + "'," + groupId + ",'','','" + date + "')");
                            try
                            {
                                DbHelperOra.ExecuteSql(sql.ToString());
                                //this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
                                judge = true;
                            }
                            catch (Exception ex)
                            {
                                throw ex;
                            }
                        }
                    }
                }
            }
            else
            {
                if (groupinfo != null && groupinfo != "")
                {
                    RemoveRights(groupinfo.Trim());
                    judge = true;
                }
            }
            return judge;
        }


        /// <summary>
        /// 删除用户组已有权限

        /// </summary>
        /// <param name="groupName">用户组名称</param>
        public void RemoveRights(string groupinfo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from actiongroup  where group_id=(select group_id from groupmanager a where  trim(a.group_info)='" + groupinfo.Trim() + "')");
            try
            {
                int i = DbHelperOra.ExecuteSql(sql.ToString());
               // int i = this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除用户组已有pda权限
        /// </summary>
        /// <param name="groupName">用户组名称</param>
        public void RemoveRightsPDA(string groupinfo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from actiongrouppda  where group_id=(select group_id from groupmanager a where  trim(a.group_info)='" + groupinfo.Trim() + "')");
            try
            {
                int i = DbHelperOra.ExecuteSql(sql.ToString());
                // int i = this.DbHelperInstance.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 判断组名是否已经存在
        /// </summary>
        /// <param name="name">组名称</param>
        /// <returns>true，已经存在，false，不存在</returns>
        public bool findGroupByName(string info)
        {
            bool result = false;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            sql.Append("select * from groupmanager t where t.group_info='" + info + "'");
            dt = DbHelperOra.QueryTable(sql.ToString());
           // this.DbHelperInstance.Fill(dt, sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }


        /// <summary>
        /// 获得用户组ID
        /// </summary>
        /// <param name="groupName">用户组名称</param>
        /// <returns></returns>
        public string getGroupID(string groupinfo)
        {
            decimal group_id = 0;
            StringBuilder sql = new StringBuilder();
            sql.Append("select t.group_id from groupmanager t where t.group_info='" + groupinfo + "'");
            try
            {
                Object obj = DbHelperOra.GetSingle(sql.ToString());
                if (!Object.Equals(obj, null))
                {
                    group_id = Convert.ToDecimal(obj);
                }
               // group_id = this.DbHelperInstance.ExecuteScalar<decimal>(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return group_id.ToString();
        }


        /// <summary>
        /// 根据用户组info获取已经配置的权限
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable getActionStrByGroupInfo(IDictionary<string, object> where)
        {
            string info = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable(); ;
            if (where["info"] != null && where["info"].ToString() != "")
            {
                info = where["info"].ToString();
                sql.Append("select t.group_id,t.group_info,b.action_name,b.action from groupmanager t,actiongroup a,action b where t.group_id=a.group_id and trim(a.action)=trim(b.action)  and t.group_info='" + info + "'");
            }
            try
            {
                dt = DbHelperOra.QueryTable(sql.ToString());
                //this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 根据用户组info获取已经配置的pda权限
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable getActionStrByGroupInfoPDA(IDictionary<string, object> where)
        {
            string info = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable(); ;
            if (where["info"] != null && where["info"].ToString() != "")
            {
                info = where["info"].ToString();
                sql.Append("select t.group_id,t.group_info,a.action from groupmanager t,actiongrouppda a where t.group_id=a.group_id  and t.group_info='" + info + "'");
            }
            try
            {
                dt = DbHelperOra.QueryTable(sql.ToString());
                //this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }


        /// <summary>
        /// 获取菜单数据
        /// </summary>
        /// <returns>菜单数据</returns>
        public DataTable getFatherUrl()
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("select action_columnid,action_columnname from actioncolumn where  ACTION_COLUMNID not in (0,19) order by action_colsort asc ");
                dt = DbHelperOra.QueryTable(sql.ToString());
               // this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
            }
            return dt;
        }


        /// <summary>
        /// 根据菜单编号获取模块数据
        /// </summary>
        /// <param name="where">菜单编号</param>
        /// <returns>模块数据</returns>
        public DataTable getChildUrl(IDictionary<string, object> where)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            string groupid = string.Empty;
            string columnid = string.Empty;
            #region 处理参数
            if (where.ContainsKey("groupid") && where["groupid"] != null && where["groupid"].ToString() != "")
            {
                groupid = where["groupid"].ToString();
            }
            if (where.ContainsKey("columnid") && where["columnid"] != null && where["columnid"].ToString() != "")
            {
                columnid = where["columnid"].ToString();
            }
            #endregion
            sql.Append("select a.action, a.action_name from actiongroup t, action a where t.action = a.action and t.group_id = '" + groupid + "'  and a.action_columnid = '" + columnid + "' order by a.action_sort");
            try
            {
                dt = DbHelperOra.QueryTable(sql.ToString());
               // this.DbHelperInstance.Fill(dt, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        /// <summary>
        /// PDA根据当前用户及模块信息判断是否有进入此模块的权限
        /// </summary>
        /// <param name="flag">区分是用户条码还是用户名</param>
        /// <param name="user">用户</param>
        /// <param name="action">模块</param>
        /// <returns></returns>
        public string GetPDAAuthority(string flag, string user)
        {
            string result = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            string groupid = GetGroupIDByUseridOrUsername(flag, user);
            if (groupid != null && groupid != "")
            {
                sql.Append("select * from action t,actiongroup g where t.action(+)=g.action and t.action_columnid='0' and group_id='" + groupid + "'");
                try
                {
                    dt = DbHelperOra.QueryTable(sql.ToString());
                    //this.DbHelperInstance.Fill(dt, sql);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            result += dt.Rows[i]["action"].ToString() + ",";
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }


        /// <summary>
        /// 根据用户条码或者用户名获取出用户ID
        /// </summary>
        /// <param name="flag">区分是用户条码还是用户名</param>
        /// <param name="user">用户</param>
        /// <returns>用户ID</returns>
        public string GetGroupIDByUseridOrUsername(string flag, string user)
        {
            string group_id = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            if (flag == "1")//员工号登录
            {
                sql.Append("select  m.group_id from master t,mastergroup m where m.master_id=t.id and t.userid='" + user + "'");
            }
            if (flag == "2")//用户名密码登录
            {
                sql.Append("select  m.group_id from master t,mastergroup m where m.master_id=t.id and t.name='" + user + "'");
            }
            if (sql != null && sql.ToString() != "")//判断是否有查询语句
            {
                try
                {
                    dt = DbHelperOra.QueryTable(sql.ToString());
                   // this.DbHelperInstance.Fill(dt, sql);
                    if (dt != null && dt.Rows.Count != 0)
                    {
                        group_id = dt.Rows[0]["group_id"].ToString();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return group_id;
        }
    }
}
